[Python Excel自動化1]以VBA運行Python操作Excel

#Python #excel #vba #自動化辦工

Posted by KitPyLearn on 2023-12-31

Excel的普及性

愛寫程式的一般上班族們應該會發現，縱使現在科技日益進步，Excel由於它的便利性，使它依舊是人們最常用的辦公軟體。

為何要使用VBA

對於不會程式的同仁來說，要他們特地打開Python來運行處理Excel資料的程式其實會不習慣，較直覺的使用方式也能提高工作效率。

為何不使用EXE取代PY

對於非資訊背景與非資訊部門來說(對 就是我)，PY檔其實會較為方便做任何修正。而且對於我個人來說，寫法也比較不偏好於寫成函數形式，寫成函數或類的確較為方便調用，但也不太方便新手去理解。

1.如何以Python操作Excel

主要利用到xlwing與win32com.client，利用win32client讀取打開中的excel工作頁，並使用xlwings進行寫入，即可把dataframe寫進excel

import pandas as pd
import win32com.client as win32
import xlwings as xw
ExcelDrive=win32.Dispatch("Excel.Application")
ExcelPath=xw.books.active.fullname
ExcelPath=ExcelPath.split('\\')
ExcelPath=ExcelPath[-1]
book_main=xw.books(ExcelPath)
book_main_1=book_main.sheets('工作表1')
book_main_1.clear_contents()

test_data=pd.DataFrame(zip(['AAPL','MSFT'],['Tech','Tech']),columns=['comp','ind'])
book_main_1['A1'].options(pd.DataFrame,header=1,index=False,expand='table').value=test_data

2.利用VBA呼叫PY檔

在Excel插入按鈕並使用以下VBA代碼

Sub 按鈕1_Click()
Dim pythonPath As String
Dim scriptPath As String

pythonPath = "C:\Users\user1\anaconda3\envs\PY38_WORK\python.exe"
scriptPath = "C:\Users\user1\Desktop\20238after\PersonalBlog\PythonExcelAuto1\PythonExcelAuto1.py"
'cmd = "cmd.exe /k " & pythonPath & " " & scriptPath 若使用此句在運行後可保留cmd介面觀察錯誤訊息
cmd = pythonPath & " " & scriptPath
Call Shell(cmd, vbNormalFocus)
End Sub

另外要注意的是若路徑存在空白或中文，VBA需使用"""才能成功讀取，可參考以下代碼

Sub 按鈕1_Click()
Dim pythonPath As String
Dim scriptPath As String

pythonPath = "C:\Users\user1\anaconda3\envs\PY38_WORK\python.exe"
scriptPath = """C:\Users\user1\Desktop\20238after\PersonalBlog\PythonExcelAuto1\PythonExcelAuto 1 中文空格測試.py"""
'cmd = "cmd.exe /k " & pythonPath & " " & scriptPath 可保留cmd介面觀察錯誤訊息
cmd = pythonPath & " " & scriptPath
Call Shell(cmd, vbNormalFocus)
End Sub

再試一下以Python yfinance提取AAPL與TSM的股票價格，並利用VBA調用之效果

import pandas as pd
import win32com.client as win32
import xlwings as xw
import yfinance as yf
ExcelDrive=win32.Dispatch("Excel.Application")
ExcelPath=xw.books.active.fullname
ExcelPath=ExcelPath.split('\\')
ExcelPath=ExcelPath[-1]
book_main=xw.books(ExcelPath)
book_main_1=book_main.sheets('工作表1')
book_main_1.clear_contents()

stk_data=['AAPL','TSM']
stk_data=yf.download(" ".join(stk_data),start="2023-01-01")
stk_data=stk_data['Adj Close']
stk_data=stk_data.reset_index()
book_main_1['A1'].options(pd.DataFrame,header=1,index=False,expand='table').value=stk_data

當初在嘗試VBA調用Python時其實有發現到Python的Package有部分會跟VBA衝突，即調用後cmd介面會直接閃退，故最後附上目前個人的所有Package列表供參考(Python 3.8)

name: py38_work
channels:
  - anaconda
  - conda-forge
  - defaults
dependencies:
  - appdirs=1.4.4=pyh9f0ad1d_0
  - beautifulsoup4=4.12.2=pyha770c72_0
  - brotli=1.0.9=hcfcfb64_9
  - brotli-bin=1.0.9=hcfcfb64_9
  - brotli-python=1.0.9=py38hd3f51b4_9
  - bzip2=1.0.8=h8ffe710_4
  - ca-certificates=2023.01.10=haa95532_0
  - certifi=2022.12.7=py38haa95532_0
  - cffi=1.15.1=py38h57701bc_3
  - charset-normalizer=3.2.0=pyhd8ed1ab_0
  - cryptography=41.0.3=py38h95f5157_0
  - cycler=0.11.0=pyhd8ed1ab_0
  - et_xmlfile=1.1.0=py38haa95532_0
  - flit-core=3.6.0=pyhd3eb1b0_0
  - fonttools=4.42.1=py38h91455d4_0
  - freetype=2.12.1=h546665d_1
  - frozendict=2.3.8=py38h91455d4_0
  - gettext=0.21.1=h5728263_0
  - glib=2.76.4=h12be248_0
  - glib-tools=2.76.4=h12be248_0
  - gst-plugins-base=1.22.5=h001b923_0
  - gstreamer=1.22.5=hb4038d2_0
  - html5lib=1.1=pyh9f0ad1d_0
  - icu=72.1=h63175ca_0
  - idna=3.4=pyhd8ed1ab_0
  - intel-openmp=2023.2.0=h57928b3_49496
  - joblib=1.1.1=py38haa95532_0
  - kiwisolver=1.4.5=py38hb1fd069_0
  - krb5=1.20.1=heb0366b_0
  - lcms2=2.15=h3e3b177_1
  - lerc=4.0.0=h63175ca_0
  - libblas=3.9.0=17_win64_mkl
  - libbrotlicommon=1.0.9=hcfcfb64_9
  - libbrotlidec=1.0.9=hcfcfb64_9
  - libbrotlienc=1.0.9=hcfcfb64_9
  - libcblas=3.9.0=17_win64_mkl
  - libclang=16.0.6=default_heb8d277_1
  - libclang13=16.0.6=default_hc80b9e7_1
  - libdeflate=1.18=hcfcfb64_0
  - libffi=3.4.2=h8ffe710_5
  - libglib=2.76.4=he8f3873_0
  - libhwloc=2.9.2=nocuda_h15da153_1008
  - libiconv=1.17=h8ffe710_0
  - libjpeg-turbo=2.1.5.1=hcfcfb64_0
  - liblapack=3.9.0=17_win64_mkl
  - libogg=1.3.4=h8ffe710_1
  - libpng=1.6.39=h19919ed_0
  - libsqlite=3.43.0=hcfcfb64_0
  - libtiff=4.5.1=h6c8260b_1
  - libvorbis=1.3.7=h0e60522_0
  - libwebp-base=1.3.1=hcfcfb64_0
  - libxcb=1.15=hcd874cb_0
  - libxml2=2.11.5=hc3477c8_1
  - libxslt=1.1.37=h6070c61_1
  - libzlib=1.2.13=hcfcfb64_5
  - lxml=4.9.3=py38h5a3a0f9_0
  - m2w64-gcc-libgfortran=5.3.0=6
  - m2w64-gcc-libs=5.3.0=7
  - m2w64-gcc-libs-core=5.3.0=7
  - m2w64-gmp=6.1.0=2
  - m2w64-libwinpthread-git=5.0.0.4634.697f757=2
  - matplotlib=3.5.3=py38haa244fe_2
  - matplotlib-base=3.5.3=py38h3268a40_2
  - mkl=2022.1.0=h6a75c08_874
  - msys2-conda-epoch=20160418=1
  - multitasking=0.0.9=pyhd8ed1ab_0
  - munkres=1.1.4=pyh9f0ad1d_0
  - numpy=1.19.5=py38hec0b029_3
  - openjpeg=2.5.0=ha2aaf27_2
  - openpyxl=3.0.10=py38h2bbff1b_0
  - openssl=3.1.3=hcfcfb64_0
  - packaging=23.1=pyhd8ed1ab_0
  - pandas=1.4.4=py38h0ae2778_0
  - patsy=0.5.3=pyhd8ed1ab_0
  - pcre2=10.40=h17e33f8_0
  - pillow=10.0.0=py38ha7eb54a_0
  - pip=23.2.1=pyhd8ed1ab_0
  - ply=3.11=py_1
  - pthread-stubs=0.4=hcd874cb_1001
  - pthreads-win32=2.9.1=hfa6e2cd_3
  - pycparser=2.21=pyhd8ed1ab_0
  - pyodbc=4.0.34=py38hd77b12b_0
  - pyparsing=3.1.1=pyhd8ed1ab_0
  - pyqt=5.15.9=py38hd6c051e_4
  - pyqt5-sip=12.12.2=py38hd3f51b4_4
  - pyreadstat=1.2.3=py38h84023d3_0
  - pysocks=1.7.1=pyh0701188_6
  - python=3.8.17=h4de0772_0_cpython
  - python-dateutil=2.8.2=pyhd8ed1ab_0
  - python-tzdata=2023.3=pyhd8ed1ab_0
  - python_abi=3.8=3_cp38
  - pytz=2023.3=pyhd8ed1ab_0
  - pywin32=305=py38h2bbff1b_0
  - qt-main=5.15.8=h2c8576c_12
  - requests=2.31.0=pyhd8ed1ab_0
  - scikit-learn=1.2.0=py38hd77b12b_1
  - scipy=1.8.1=py38h4990095_2
  - seaborn=0.12.2=hd8ed1ab_0
  - seaborn-base=0.12.2=pyhd8ed1ab_0
  - setuptools=68.1.2=pyhd8ed1ab_0
  - sip=6.7.11=py38hd3f51b4_0
  - soupsieve=2.3.2.post1=pyhd8ed1ab_0
  - statsmodels=0.13.5=py38h080aedc_1
  - tbb=2021.10.0=h91493d7_0
  - threadpoolctl=2.2.0=pyh0d69192_0
  - tk=8.6.12=h8ffe710_0
  - toml=0.10.2=pyhd8ed1ab_0
  - tomli=2.0.1=pyhd8ed1ab_0
  - tornado=6.3.3=py38h91455d4_0
  - typing_extensions=4.4.0=py38haa95532_0
  - ucrt=10.0.22621.0=h57928b3_0
  - unicodedata2=15.0.0=py38h91455d4_0
  - vc=14.3=h64f974e_17
  - vc14_runtime=14.36.32532=hfdfe4a8_17
  - vs2015_runtime=14.36.32532=h05e6639_17
  - webencodings=0.5.1=py_1
  - wheel=0.41.2=pyhd8ed1ab_0
  - win_inet_pton=1.1.0=pyhd8ed1ab_6
  - xlwings=0.30.10=py38haa244fe_0
  - xorg-libxau=1.0.11=hcd874cb_0
  - xorg-libxdmcp=1.1.3=hcd874cb_0
  - xz=5.2.6=h8d14728_0
  - yfinance=0.2.28=pyhd8ed1ab_0
  - zlib=1.2.13=hcfcfb64_5
  - zstd=1.5.2=h12be248_7
  - pip:
    - absl-py==0.15.0
    - astunparse==1.6.3
    - cachetools==5.3.1
    - flatbuffers==1.12
    - gast==0.4.0
    - google-auth==2.22.0
    - google-auth-oauthlib==0.4.6
    - google-pasta==0.2.0
    - grpcio==1.34.1
    - h5py==3.1.0
    - importlib-metadata==6.8.0
    - keras-nightly==2.5.0.dev2021032900
    - keras-preprocessing==1.1.2
    - markdown==3.4.4
    - markupsafe==2.1.3
    - oauthlib==3.2.2
    - opt-einsum==3.3.0
    - protobuf==3.20.3
    - pyasn1==0.5.0
    - pyasn1-modules==0.3.0
    - requests-oauthlib==1.3.1
    - rsa==4.9
    - six==1.15.0
    - tensorboard==2.11.2
    - tensorboard-data-server==0.6.1
    - tensorboard-plugin-wit==1.8.1
    - tensorflow-estimator==2.5.0
    - tensorflow-gpu==2.5.0
    - termcolor==1.1.0
    - typing-extensions==3.7.4.3
    - urllib3==1.26.16
    - werkzeug==2.3.7
    - wrapt==1.12.1
    - zipp==3.16.2
prefix: C:\Users\user1\anaconda3\envs\py38_work

#Python #excel #vba #自動化辦工

KitPyLearn
澳門人，愛寫代碼，曾有ETF成分資料庫維護經驗，菜鳥研究員，整理筆記記錄所學




Related Posts

4. Builder

4. Builder

 WayneCheng
如何使用 K8S 自動化定期 CronJob 抓網路公開資料

如何使用 K8S 自動化定期 CronJob 抓網路公開資料

 KD Chang
CS50 linked list

CS50 linked list

 adrianwudev

Comments